from config import SQLITE_CONF
from mysqlx import init_db, dbx, db
# from db_test import create_truncate_table


def create_truncate_table(table):
    db.truncate_table(table)


def full_test():
    create_truncate_table('user')
    #######################################################################################################

    rowcount = db.insert('user', name='张三', age=55, birth_date='1968-10-08', sex=0, grade=1.0, point=20.5, money=854.56)
    assert rowcount == 1, 'insert'
    assert dbx.sql('user.user_count').get() == 1, 'insert'

    id2 = dbx.sql('user.batch_insert').save('张三', 55, '1968-10-08', 0, 1.0, 20.5, 854.56)
    assert id2 > 0, 'save'
    assert dbx.sql('user.user_count').get() == 2, 'save'

    dbx.sql('user.user_update').execute('王五', id2)
    assert dbx.sql('user.select_name').get(id2) == '王五', 'execute'

    dbx.sql('user.user_update2').execute(name='赵六', id=id2)
    assert dbx.sql('user.named_select').query_one(id=id2)['name'] == '赵六', 'execute'

    print(dbx.sql('user.named_select').select_one(id=id2))

    args = [
        ('张三', 55, '1968-10-08', 0, 1.0, 20.5, 854.56),
        ('张三', 55, '1968-10-08', 0, 1.0, 20.5, 854.56)
    ]
    dbx.sql('user.batch_insert').batch_execute(args)
    users = dbx.sql('user.select_all').select()
    assert len(users) == 4, 'batch_execute'
    users = dbx.sql('user.select_all').query()
    assert len(users) == 4, 'batch_execute'

    users = dbx.sql('user.named_select').select(id=id2)
    assert len(users) == 1, 'select'
    users = dbx.sql('user.named_select').query(id=id2)
    assert len(users) == 1, 'query'

    users = dbx.sql('user.select_name').select(id=id2)
    assert len(users) == 1, 'select'
    users = dbx.sql('user.select_name').query(id=id2)
    assert len(users) == 1, 'query'

    users = dbx.sql('user.named_select').select(id=id2)
    assert len(users) == 1, 'select'
    users = dbx.sql('user.named_select').query(id=id2)
    assert len(users) == 1, 'query'

    dbx.sql('user.delete').execute(id2)
    assert dbx.sql('user.user_count').get() == 3, 'execute delete'

    args = [
        {'name': '李四', 'age': 55, 'birth_date': '1968-10-08', 'sex': 0, 'grade': 1.0, 'point': 20.5, 'money': 854.56},
        {'name': '李四', 'age': 55, 'birth_date': '1968-10-08', 'sex': 0, 'grade': 1.0, 'point': 20.5, 'money': 854.56}
    ]
    db.batch_insert('user', args)
    assert dbx.sql('user.user_count').get() == 5, 'batch_insert'

    dbx.sql('user.batch_insert2').batch_execute(args)
    assert dbx.sql('user.user_count').get() == 7, 'batch_execute'

    for u in dbx.sql('user.select_all').page(page_num=2, page_size=3).select():
        print(u)

    for u in dbx.sql('user.select_all').page(page_num=2, page_size=3).query():
        print(u)

    print(dbx.sql('user.batch_insert').save('张三', 55, '1968-10-08', 0, 1.0, 20.5, 854.56))
    print(dbx.sql('user.batch_insert2').save(**{'name': '李四', 'age': 55, 'birth_date': '1968-10-08', 'sex': 0, 'grade': 1.0, 'point': 20.5, 'money': 854.56}))


if __name__ == '__main__':
    init_db('/Users/summy/project/python/sqlx-exec/test/test.db', **SQLITE_CONF)

    full_test()

